const express = require('express')
const conn = require('../util/sql.js')

// 使用express下的Router方法，设置路由
const router = express.Router()
router.use(express.urlencoded())

// 实现文章分类/index/category 接口
router.get('/category', (req, res) => {
    const sqlStr = `select id,name from categories`
    conn.query(sqlStr, (err, result) => {
        console.log(err);
        console.log(result);
        if (err) return res.status(500).json({ status: 500, message: "服务器出错" })
        res.status(200).json({ status: 0, message: "获取文章分类列表成功！", data: result })
    })
})

// 实现文章热点图/index/hotpic 接口
router.get('/hotpic', (req, res) => {
    const sqlStr = `select id,cover from articles order by rand() limit 5`

    // 根据read字段查询表中7条数据
    // SELECT id, title FROM `articles`
    // order by `read`
    // desc limit 7

    // 随机查询表中5条数据
    // SELECT * FROM articles ORDER BY RAND() LIMIT 5
    conn.query(sqlStr, (err, result) => {
        console.log(err);
        console.log(result);
        if (err) return res.status(500).json({ status: 500, message: "服务器出错" })
        res.status(200).json({ status: 0, message: "获取文章热点图成功！", data: result })
    })
})

// 文章热门排行 /index/rank 接口
router.get('/rank', (req, res) => {
    const sqlStr = `select id,title from articles where state="已发布" order by "read" desc limit 7`

    conn.query(sqlStr, (err, result) => {
        console.log(err);
        console.log(result);
        if (err) return res.status(500).json({ status: 500, message: "服务器出错" })
        res.status(200).json({ status: 0, message: "获取文章热门排行成功！", data: result })
    })
})

// 最新资讯 /index/latest 接口
router.get('/latest', (req, res) => {
    const sqlStr = `select * from articles where state="已发布" order by 'date' desc limit 5`

    conn.query(sqlStr, (err, result) => {
        console.log(err);
        console.log(result);
        if (err) return res.status(500).json({ status: 500, message: "服务器出错" })
        res.status(200).json({ status: 0, message: "获取最新资讯成功！", data: result })
    })
})

// 最新评论 /index/latest_comment 接口
router.get('/latest_comment', (req, res) => {
    // const sqlStr = `select author,date,content from comments where date=(select max(date) from comments) and state="已通过" order by time desc limit 6`
    // const sqlStr = `select author,date,content from comments where state="已通过" limit 6`
    const sqlStr = `select author,date,left(content,20) intro from comments order by 'date' desc limit 6`

    conn.query(sqlStr, (err, result) => {
        console.log(err);
        console.log(result);
        if (err) return res.status(500).json({ status: 500, message: "服务器出错" })
        res.status(200).json({ status: 0, message: "获取最新评论成功！", data: result })
    })
})

// 焦点关注 /index/attention 接口
router.get('/attention', (req, res) => {
    const sqlStr = `select content from articles where state="已发布" limit 7`

    conn.query(sqlStr, (err, result) => {
        console.log(err);
        console.log(result);
        if (err) return res.status(500).json({ status: 500, message: "服务器出错" })
        res.status(200).json({ status: 0, message: "获取焦点关注成功！", data: result })
    })
})

// 文章详情内容 /index/artitle 接口
router.get('/artitle', (req, res) => {

    const { id } = req.query
    const sqlStr = `select * from articles where id=${id}`

    conn.query(sqlStr, (err, result) => {
        // console.log(err);
        // console.log(result);

        // // 上一篇文章
        const sqlStr1 = `select id,title from articles where id=${id - 1}`
            // console.log(sqlStr1);
        conn.query(sqlStr1, (err, result) => {
            // console.log(result[0]);
            // console.log(`prev:${result[0]}`);
            let prev = null;
            if (result.length > 0) {
                prev = result[0];
            }

            // 下一篇文章
            const sqlStr2 = `select id,title from articles where id=${parseInt(id) + 1}`
            conn.query(sqlStr2, (err, result) => {
                let next = null
                if (result.length > 0) {
                    next = result[0];
                }
                // console.log(next);

                const sqlStr3 = `select categoryId from articles where id=${id}`
                    // console.log(sqlStr3);
                conn.query(sqlStr3, (err, result) => {
                    const sqlStr4 = `select name from categories where id=${result[0].categoryId}`
                        // console.log(sqlStr4);
                    conn.query(sqlStr4, (err, result) => {
                        // console.log(result[0]);
                        let type = result[0].name
                            // console.log(type);
                            // console.log(prev);
                            // console.log(next);

                        const sqlStr5 = `select * from articles where id=${id}`
                        conn.query(sqlStr5, (err, result) => {
                            // console.log(result[0]);
                            if (err) return res.status(500).json({ status: 500, msg: "服务器出错" })
                            console.log(result.length);
                            if (result.length > 0) {
                                res.status(200).json({
                                    status: 0,
                                    msg: "获取文章详情成功",
                                    data: {
                                        title: result[0].title,
                                        author: result[0].author,
                                        type: type,
                                        date: result[0].date,
                                        read: result[0].read,
                                        content: result[0].content,
                                        prev: prev,
                                        next: next
                                    }
                                })
                            } else {
                                res.status(201).json({ status: 1, msg: "获取文章详情失败" })
                            }
                        })
                    })
                })

            })
        })


        // // if (err) return res.status(500).json({ status: 500, message: "服务器出错" })
        // res.status(200).json({ status: 0, message: "获取焦点关注成功！", data: result[0] })
    })
})

// 文章搜索 /index/search  接口
router.get('/search', (req, res) => {
    const { key, page, perpage } = req.query

    // 获取news表中对应条件的数据
    let sql = `select * from news`;
    // 模糊查询，匹配title
    if (key) sql += ` where title like "%${key}%"`
        // 限制数据显示条数
    sql += ` limit ${(page - 1)*perpage},${perpage}`

    conn.query(sql, (err, result) => {
        console.log(err);
        if (err) return res.status(500).json({ status: 500, msg: "服务器出错" })

        // 保存第一次查询到的数据，因为还要计算总数，所以还有一盒查询语句要写
        let news = result;

        // 第二条sql语句，获取对应条件的数据总条数
        let sql = `select count(*) as totalCount from news`;

        // 使用模糊查询，匹配title
        if (key) sql += ` where title like "%${key}%"`

        conn.query(sql, (err, result) => {
            if (err) return res.status(500).json({ status: 500, msg: "服务器出错" })

            res.status(200).json({
                code: 200,
                msg: "搜索文章成功",
                data: news,
                totalCount: result[0].totalCount
            })
        })

    })
})

module.exports = router